<?php
/**
 * 执行会员系统数据库迁移
 * 在根目录下运行: php database/migrate_membership.php
 */

// 数据库配置
$dbConfig = [
    'host' => '101.35.252.112',
    'port' => '45508',
    'database' => 'trace',
    'username' => 'root',
    'password' => 'CeyZFHxjPABhp2Hz',
    'charset' => 'utf8mb4',
    'prefix' => 'gf_'
];

try {
    // 创建PDO连接
    $dsn = "mysql:host={$dbConfig['host']};port={$dbConfig['port']};dbname={$dbConfig['database']};charset={$dbConfig['charset']}";
    $pdo = new PDO($dsn, $dbConfig['username'], $dbConfig['password'], [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    ]);
    
    echo "数据库连接成功\n";
    
    // 会员系统数据库迁移SQL
    $sql = "
-- 会员套餐配置表
CREATE TABLE IF NOT EXISTS gf_membership_plans (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  plan_type VARCHAR(50) NOT NULL COMMENT '套餐类型: basic, premium, enterprise',
  name VARCHAR(100) NOT NULL COMMENT '套餐名称',
  description TEXT COMMENT '套餐描述',
  price DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '价格（元/月）',
  app_limit INT NOT NULL DEFAULT 1 COMMENT '应用数量限制',
  features JSON COMMENT '功能特性配置',
  is_active TINYINT(1) DEFAULT 1 COMMENT '是否启用',
  sort_order INT DEFAULT 0 COMMENT '排序',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_plan_type (plan_type)
);

-- 插入默认套餐配置
INSERT INTO gf_membership_plans (plan_type, name, description, price, app_limit, features, sort_order) VALUES
('basic', '基础版', '适合个人开发者，免费使用，限制1个应用', 0.00, 1, '{\"support\": \"community\", \"analytics\": \"basic\", \"storage\": \"1GB\"}', 1),
('premium', '高级版', '适合小型团队，无应用数量限制', 99.00, -1, '{\"support\": \"email\", \"analytics\": \"advanced\", \"storage\": \"10GB\", \"custom_domain\": true}', 2),
('enterprise', '企业版', '适合大型企业，提供专属服务', 999.00, -1, '{\"support\": \"dedicated\", \"analytics\": \"enterprise\", \"storage\": \"unlimited\", \"custom_domain\": true, \"api_rate_limit\": \"high\"}', 3);

-- 用户会员信息表
CREATE TABLE IF NOT EXISTS gf_user_memberships (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL COMMENT '用户ID',
  plan_type VARCHAR(50) NOT NULL COMMENT '套餐类型',
  status VARCHAR(20) DEFAULT 'active' COMMENT '状态: active, expired, cancelled',
  start_date DATE NOT NULL COMMENT '开始日期',
  end_date DATE NOT NULL COMMENT '结束日期',
  auto_renew TINYINT(1) DEFAULT 1 COMMENT '是否自动续费',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_user_id (user_id),
  INDEX idx_status_end_date (status, end_date),
  FOREIGN KEY (plan_type) REFERENCES gf_membership_plans(plan_type)
);

-- 支付参数配置表（易支付）
CREATE TABLE IF NOT EXISTS gf_payment_config (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  provider VARCHAR(50) NOT NULL DEFAULT 'yipay' COMMENT '支付提供商',
  config_key VARCHAR(100) NOT NULL COMMENT '配置键',
  config_value TEXT COMMENT '配置值',
  is_active TINYINT(1) DEFAULT 1 COMMENT '是否启用',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_provider_key (provider, config_key)
);

-- 插入易支付默认配置
INSERT INTO gf_payment_config (provider, config_key, config_value, is_active) VALUES
('yipay', 'api_url', 'https://pay.gggua.com/', 1),
('yipay', 'merchant_id', '1295', 1),
('yipay', 'merchant_key', 'pD7J5Cz5755oFp5o0AaAhzaAnp45En4O', 1),
('yipay', 'notify_url', '/api/payment/notify', 1),
('yipay', 'return_url', '/payment/success', 1);

-- 用户余额表
CREATE TABLE IF NOT EXISTS gf_user_balances (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL COMMENT '用户ID',
  balance DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '余额（元）',
  frozen_balance DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '冻结余额',
  total_recharge DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '总充值金额',
  total_consume DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '总消费金额',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_user_id (user_id)
);

-- 账单记录表
CREATE TABLE IF NOT EXISTS gf_billing_records (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL COMMENT '用户ID',
  bill_type VARCHAR(50) NOT NULL COMMENT '账单类型: recharge, consumption, refund',
  bill_subtype VARCHAR(50) COMMENT '子类型: membership, balance, service',
  amount DECIMAL(10,2) NOT NULL COMMENT '金额',
  balance_before DECIMAL(10,2) NOT NULL COMMENT '操作前余额',
  balance_after DECIMAL(10,2) NOT NULL COMMENT '操作后余额',
  related_id BIGINT COMMENT '关联ID（如会员ID、订单ID）',
  description TEXT COMMENT '描述',
  payment_method VARCHAR(50) COMMENT '支付方式',
  transaction_id VARCHAR(100) COMMENT '第三方交易ID',
  status VARCHAR(20) DEFAULT 'completed' COMMENT '状态: pending, completed, failed, refunded',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_user_id (user_id),
  INDEX idx_bill_type (bill_type),
  INDEX idx_created_at (created_at),
  INDEX idx_transaction_id (transaction_id)
);

-- 会员订单表
CREATE TABLE IF NOT EXISTS gf_membership_orders (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL COMMENT '用户ID',
  plan_type VARCHAR(50) NOT NULL COMMENT '套餐类型',
  order_no VARCHAR(64) NOT NULL COMMENT '订单号',
  amount DECIMAL(10,2) NOT NULL COMMENT '订单金额',
  discount_amount DECIMAL(10,2) DEFAULT 0.00 COMMENT '折扣金额',
  final_amount DECIMAL(10,2) NOT NULL COMMENT '实际支付金额',
  months INT NOT NULL DEFAULT 1 COMMENT '购买月数',
  start_date DATE COMMENT '会员开始日期',
  end_date DATE COMMENT '会员结束日期',
  payment_method VARCHAR(50) COMMENT '支付方式',
  payment_status VARCHAR(20) DEFAULT 'pending' COMMENT '支付状态',
  transaction_id VARCHAR(100) COMMENT '第三方交易ID',
  auto_renew TINYINT(1) DEFAULT 1 COMMENT '是否自动续费',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_order_no (order_no),
  INDEX idx_user_id (user_id),
  INDEX idx_payment_status (payment_status),
  INDEX idx_created_at (created_at)
);

-- 自动扣费记录表
CREATE TABLE IF NOT EXISTS gf_auto_charge_records (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL COMMENT '用户ID',
  membership_id BIGINT NOT NULL COMMENT '会员ID',
  amount DECIMAL(10,2) NOT NULL COMMENT '扣费金额',
  discount_rate DECIMAL(5,2) DEFAULT 100.00 COMMENT '折扣率（百分比）',
  final_amount DECIMAL(10,2) NOT NULL COMMENT '实际扣费金额',
  charge_type VARCHAR(50) DEFAULT 'membership_renew' COMMENT '扣费类型',
  status VARCHAR(20) DEFAULT 'success' COMMENT '状态: success, failed, pending',
  error_message TEXT COMMENT '错误信息',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user_id (user_id),
  INDEX idx_membership_id (membership_id),
  INDEX idx_created_at (created_at)
);

-- 系统配置表
CREATE TABLE IF NOT EXISTS gf_system_config (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  config_key VARCHAR(100) NOT NULL COMMENT '配置键',
  config_value TEXT COMMENT '配置值',
  config_type VARCHAR(50) DEFAULT 'string' COMMENT '配置类型: string, int, float, json, bool',
  description TEXT COMMENT '配置描述',
  is_system TINYINT(1) DEFAULT 0 COMMENT '是否系统配置',
  is_active TINYINT(1) DEFAULT 1 COMMENT '是否启用',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_config_key (config_key)
);

-- 插入系统默认配置
INSERT INTO gf_system_config (config_key, config_value, config_type, description, is_system) VALUES
('balance_renew_discount', '90', 'int', '余额自动续费折扣（百分比）', 1),
('auto_renew_before_days', '7', 'int', '会员到期前多少天开始自动续费', 1),
('max_failed_charge_attempts', '3', 'int', '自动扣费最大失败尝试次数', 1),
('min_balance_for_renew', '50.00', 'float', '自动续费最低余额要求', 1),
('enable_auto_renew', '1', 'bool', '是否启用自动续费功能', 1),
('enable_balance_payment', '1', 'bool', '是否启用余额支付', 1);

-- 用户操作日志表
CREATE TABLE IF NOT EXISTS gf_user_logs (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL COMMENT '用户ID',
  action VARCHAR(100) NOT NULL COMMENT '操作类型',
  resource_type VARCHAR(50) COMMENT '资源类型',
  resource_id BIGINT COMMENT '资源ID',
  old_data JSON COMMENT '旧数据',
  new_data JSON COMMENT '新数据',
  ip_address VARCHAR(45) COMMENT 'IP地址',
  user_agent TEXT COMMENT '用户代理',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user_id (user_id),
  INDEX idx_action (action),
  INDEX idx_created_at (created_at),
  INDEX idx_resource (resource_type, resource_id)
);

-- 创建索引优化查询
CREATE INDEX idx_user_membership_active ON gf_user_memberships(user_id, status, end_date);
CREATE INDEX idx_billing_date ON gf_billing_records(user_id, created_at);
CREATE INDEX idx_balance_updated ON gf_user_balances(user_id, updated_at);
    ";
    
    // 开始事务
    $pdo->beginTransaction();
    
    // 执行SQL语句
    $pdo->exec($sql);
    
    // 提交事务
    $pdo->commit();
    
    echo "✅ 会员系统数据库表创建成功！\n";
    echo "✅ 默认会员套餐已插入\n";
    echo "✅ 易支付配置已插入\n";
    echo "✅ 系统配置已插入\n";
    
} catch (PDOException $e) {
    echo "❌ 数据库执行失败: " . $e->getMessage() . "\n";
    if (isset($pdo) && $pdo->inTransaction()) {
        $pdo->rollBack();
    }
    exit(1);
} catch (Exception $e) {
    echo "❌ 执行失败: " . $e->getMessage() . "\n";
    if (isset($pdo) && $pdo->inTransaction()) {
        $pdo->rollBack();
    }
    exit(1);
}

echo "\n🎉 数据库迁移完成！\n";